<?php

class getDataIocTask extends baseWemTask
{
    
    private $localDir;
    private $localTmpDir;
    private $url;
    private $remoteDir;
    private $ftpUser;
    private $ftpPass;
    private $urlComp;
    private $objReader;
    private $filename;
    
    static public $samIOC_Plant = array(
        "Gravina-Anfiteatro"=>"Gravina_1",
        "Helios Italquarz"=>"Helios",
        "Lequile130"=>"Lequile_1",
        "Lequile131"=>"Lequile_2",
        "Ula Tirso 1"=>"Valle_Tirso_1",
        "Ula Tirso 2"=>"Valle_Tirso_2"
    );
        
        
    static public $samIOC_Dev = array(
        "Energy Meter"=>"Power Meter",
        "Weather"=>"Stazione Meteo",
        "InverterString"=>"Inverter Grid",
        "InverterCentral"=>"Inverter Centralizzati",
        "MV Protection"=>"Protezioni",
        "String Comb"=>"String Combinator"
        );
    
    //each key must be unique
    static public $samIOC_Var = array(
        "address"=>"Guipath",
        "sampleTime"=>"Data",
        "ActExpEn"=>"Tot Energia Attiva Esportata (kWh)",
        "ActImpEn"=>"Tot Energia Attiva Importata (kWh)",
        "Pac"=>"Tot Potenza Attiva Esportata (W)",
        "Irr"=>"Irr totale (W/mq)",
        "Tmod"=>"Temp Pannello (°C)",
        "Tenv"=>"Temp Ambiente (°C)",
        "windSpeed"=>"Velocita vento (m/sec)",
        "Vac12"=>"Tensione L1-L2 (V)",
        "Vac23"=>"Tensione L2-L3 (V)",
        "Vac13"=>"Tensione L3-L4 (V)",
        "f"=>"Frequenza (Hz)",
        "Idc"=>"Tot Corrente (A)",
        "Vdc"=>"Tot Tensione (V)",
        "ProdEn"=>"Tot Produzione (kWh)",
        "Pac"=>"Potenza in uscita (W)",
        "Iac"=>"Corrente in uscite (A)",
        "Vac"=>"Tensione in uscita (V)",
        "Vdc1"=>"Tensione Ingresso 1 (V)",
        "Idc1"=>"Corrente Ingresso 1 (V)",
        "Vdc2"=>"Tensione Ingresso 2 (V)",
        "Idc2"=>"Corrente Ingresso 2 (V)",);
    
    
    public function configure()
    {
        parent::configure();
        
        $this->name      = 'getData-ioc';
        $this->briefDescription = 'Get Data from the Samares IOC system';
        
        $this->localDir="//192.168.0.37/om/NUOVO SERVER O&M/6_MONITORAGGIO/1_DATA/";
        $this->localTmpDir="C:/wamp/www/seth/data/tmp/";
        $this->url='ftp://192.168.0.137:21';
        $this->remoteDir="/ioc/";
        $this->ftpUser="root";
        $this->ftpPass="Feynman_65";
        
        
        //ARGUMENTS
        $this->addArgument('plant',  sfCommandArgument::REQUIRED,'Plant to check');
        $this->addArgument('start',  sfCommandArgument::OPTIONAL,'The starting day [Format: Y-m-d]',0);
        $this->addArgument('days',  sfCommandArgument::OPTIONAL,'Number of days to check [the greater the range the longer the script..] ',5);
    }
    public function execute($arguments = array(), $options = array())
    {   
        //Get DB Connection
        $this->ConnectDB();
        
        //register task execution
        $tasklog=seth::regTaskStart($this->name, $arguments, $options );

        
        //get the requested plant
        $plant=new Plant();
        $plant=Doctrine_Core::getTable('plant')->find($arguments['plant']);
        if (!is_object($plant)){
            $this->logBlock("The provided plant does not exist", "ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }
       
        //register log file
        $this->addFileLogger($plant->getName());
        
        
        $this->localDir=$this->localDir."/".$plant->getName();
        if (!is_dir($this->localDir)){
            if(!isset($fs))
                $fs=new sfFilesystem();
            $fs->mkdirs($this->localDir);
        }
        $this->localDir=$this->localDir."/";
            
        if(filter_var($this->url, FILTER_VALIDATE_URL) === FALSE)
        { 
            $this->logBlock("The provided URL is not valid", "ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }
        
        $this->urlComp=parse_url($this->url);
        if (!(isset($this->urlComp['scheme']) and isset($this->urlComp['host']) and isset($this->urlComp['port'])))
        {
            $this->logBlock("The provided URL is not complete. \n You must provide both the SCHEME (Ex. ftp or http) and the HOST (Ex. 192.168.0.1 or www.google.it) and the PORT (Ex 21). \n\n Example: ftp://ftp.power-one.it:21", "ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        } 
            
       /*
        * FTP CONNECTION
        */
        $this->logBlock("Trying to connect to ".$this->url."...","INFO");
        $ftp=new wemFtp();
        
        // OPEN FTP SOCKET
        if ($ftp->open($this->urlComp['host'], $this->urlComp['port']))
         {
            $this->logBlock("Connection to ".$this->url." established.\n Trying to authenticate...","INFO");
         } else {
             $this->logBlock("Connection to ".$this->url." failed. URL is unreachable","ERROR");
             seth::regTaskEnd($tasklog, false);
             die();
        }
        
        //LOGIN
        if ($ftp->login($this->ftpUser, $this->ftpPass)) {
            $this->logBlock("Connected as ".$this->ftpUser."@".$this->url,"INFO");
        } else {
            $this->logBlock("Username or password invalid\n","ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }
        
        // SET PASSIVE MODE
        if ($ftp->setPassv())
            $this->logBlock("Turned in PASSIVE mode.","INFO");
        else
            {
            $this->logBlock("Passive mode Error. Connection aborted. ","ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }
        
        //RETRIEVE FILE LIST
        $files=$ftp->getList($this->remoteDir);
        if (!$files){
            $this->logBlock("Connection to ".$this->url." failed getting files list. URL is unreachable","ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }

        //SEARCH FOR DATA TO DOWNLOAD
        //For each plant's device it gets from the last updated record in the var"Dev_Type" table:
        //  - Last Sample Time
        //  - Last Source
        //  - Last Soure Id
        // For each plant device type (Meter, Inverter...) collects the previous info in this way:
        //  - Last Sample Time: the lowest sample time of all the device of that type
        //  - Last Source: the source should be the same for each device type
        //  - Last Source Id: the greatest Id for each type
       
        
        /* TIME RANGE DEFINITION */
        if ($arguments["start"]==0)
            $dStart=new DateTime();
        elseif (strtotime($arguments["start"]) == FALSE){
            $this->logBlock("Argument [start] provided is not valid. Must be a number >0","ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }else
            $dStart=new DateTime($arguments["start"]);
            
        if (!is_numeric($arguments["days"]) or $arguments["days"]<=0){
            $this->logBlock("Argument [days] provided is not valid. Must be a number >0","ERROR");
            seth::regTaskEnd($tasklog, false);
            die();
        }else
            $days=$arguments["days"];
        
        $rangeLimit=$dStart->sub(new DateInterval("P".$days."D"));
        
        if (key_exists($plant->getName(), self::$samIOC_Plant))
               $plantName=self::$samIOC_Plant[$plant->getName()];
           else
               $plantName=str_replace(" ", "_", $plant->getName());
        

        $plantLastUpdate=$plant->getLastUpdate();
        if (!isset($plantLastUpdate)){
            $plantLastUpdate=new DateTime();
            $plantLastUpdate->setTimestamp(0);
        }
        $plantLastUpdate=$plantLastUpdate>=$rangeLimit?$plantLastUpdate:$rangeLimit;
        $today=new DateTime();
        $today->setTime("00","00","00");
        
        $plantKnownSources=$plant->getKnownSources();

        $devs=$plant->getMonitoredPowerDev();
                
        //IOC offre un file per ogni impianto per ogni giorno, uno sheet per ogni tipo di dispositivo, aggiornato ogni 15 minuti
              
        $updateFlag=false;
        foreach ($files as $file){
            
           $this->filename=substr($file,strlen($this->remoteDir));
  
           if (strripos($this->filename,$plantName) != false){
                //$file relativo a $plant
                //
                //data e ora dell'ultima modifica al file
                $fileDate=new DateTime();
                $timeStamp=$ftp->getMdTime($file);
                
                if ($timeStamp)
                    $fileDate->setTimestamp ($timeStamp); 
                else{
                     $this->logBlock("Connection to ".$this->url." failed getting last mod time. URL is unreachable","ERROR");
                     seth::regTaskEnd($tasklog, false);
                     die();
                }
                
                   /*TODO
                    * aggiungere controllo su tipo di file (es xls)
                    */
                
                if ($options['debug'])
                    echo "\n file date".$fileDate->format("Y-m-d H:i:s")." Last Update".$plantLastUpdate->format("Y-m-d H:i:s");
                if ($fileDate>$plantLastUpdate){
                    //$file dev'essere aperto

                    //FILE DOWNLOAD                  
                    if($ftp->getFile($this->localTmpDir.$this->filename, $file)){
                        
                        $this->logBlock("\n".$this->filename." succesfully downloaded.","INFO");
                        
                        //open file
                        $ts = microtime(true);
                        if (!isset($this->objReader)){
                            $this->objReader = PHPExcel_IOFactory::createReader('Excel5');
                            $this->objReader->setReadDataOnly(true);   
                        }
                        
                        //is the file already known?
                        if (count($plantKnownSources)>0){
                            foreach ($plantKnownSources as $knownSource=>$knownSourceId){
                                if (strpos($knownSource,":")){
                                list($srcWorkbook,$srcWorksheet)=explode(":",$knownSource);
                                    if ($srcWorkbook==$this->filename){
                                        //file già aperto in precedenza
                                        if (!isset($filter)){
                                            $this->logBlock("Known file, using filter from the last read line ","INFO");
                                            $filter = new IOCReadFilter(); 
                                        }
                                                
                                        $filter->AddIOCSheetFilter($knownSourceId+1, 10000 ,"A" , "J",$srcWorksheet, 2 );      
                                    } 
                                }     
                            }

                        }
                        if (!isset($filter)){
                            //standard filter: only the sheets handled and a certain number of rows and columns
                            foreach (self::$samIOC_Dev as $sheet){
                                if (!isset($filter))
                                    $filter = new IOCReadFilter();
                                $filter->AddIOCSheetFilter(1, 10000 ,"A" , "J",$sheet,2);
                            }
                            $this->logBlock("Using standard PHPExcel IOC filter","INFO");
                        }

                        if (isset($filter)){
                            $this->objReader->setReadFilter($filter);
                        }

                        $this->logBlock("Opening ".$this->localTmpDir.$this->filename,"INFO");
                        $PHPWorkbook = $this->objReader->load($this->localTmpDir.$this->filename);

                        if ($PHPWorkbook)
                        {
                            $this->logBlock($this->filename." succesfully opened in ".round(microtime(true)-$ts)." s","INFO");

                            foreach ($PHPWorkbook->getWorksheetIterator() as $PHPWorksheet){
                                
                                if ($options['debug'])
                                 echo "\nMem usage is: ",(memory_get_usage()/1024), "KB \n";
                                 
                                 if (array_search($PHPWorksheet->getTitle(),self::$samIOC_Dev)){
                                    $samIOC_Ind=array();
                                                                        
                                    $this->logBlock("Processing ".$PHPWorksheet->getTitle(),"INFO");
                                    
                                    foreach ($PHPWorksheet->getRowIterator() as $row){
                                        
                                        if ($row->getRowIndex()==2){
                                            //cerco gli indici delle variabili di interesse
                                            $cellIterator = $row->getCellIterator();
                                            $cellIterator->setIterateOnlyExistingCells();  
                                            foreach ($cellIterator as $cell) {
                                                
                                                $wemVar=array_search($cell->getValue(), self::$samIOC_Var);

                                                if (($wemVar)){
                                                        //samIOC_ind contiene, per ogni variabile WEM, il corrispondente indice colonna nel file excel
                                                        $samIOC_Ind[$wemVar]=$cell->columnIndexFromString($cell->getColumn());
                                                }
                                                
                                            }
                                            if (count($samIOC_Ind)==0){
                                                $this->logBlock(" Error importing data from ".$this->filename,"ERROR");
                                                break;
                                            }    
                                            unset($cellIterator);
                                        }

                                        if (($row->getRowIndex()>=4)){

                                            $addr=$PHPWorksheet->getCellByColumnAndRow($samIOC_Ind["address"]-1,$row->getRowIndex())->getValue();
                                            if ($addr<>"") {

                                                $foundDev=false;
                                                foreach ($devs as $dev){
               
                                                    if ($dev->getAddress()==$addr){
                                                        $foundDev=true;
                                                        break;
                                                    }  
                                                }     
                                                if ($foundDev){
                                                   
                                                   $type=$dev->getType();
                                                   if ($this->tryInsert($PHPWorksheet,$row,$samIOC_Ind,$dev)){
                                                        if (!$updateFlag)
                                                                $updateFlag=true;
                                                        if (isset($insertCount))
                                                                if (key_exists($type, $insertCount))
                                                                    $insertCount[$type]++;
                                                                else
                                                                    $insertCount[$type]=1;
                                                        else
                                                                $insertCount=array($type => 1); 
                                                   }
                                                   
                                                   unset($type);

                                                }else
                                                    $this->logBlock(" Device with address ".$addr." not found.","ERROR"); 
                                                
                                                unset($dev);
                                            }
                                        }
                                        
                                        
                                    }
                                    
                                }
                            }
                            
                            /* if the file is older than today
                             * move it from tmp dir to local dir and delete from FTP server
                             */

                            if ($fileDate<$today){
                                if(!isset($fs))
                                    $fs=new sfFilesystem();
                                
                                $fs->copy($this->localTmpDir.$this->filename, $this->localDir.$this->filename,array("override"=>true));
                                if (is_file($this->localDir.$this->filename)){

                                    if($ftp->delFile($file))
                                        $this->logBlock($this->filename." removed from remote FTP server ".$this->url,"INFO");
                                    else
                                        $this->logBlock(" Error removing ".$this->filename." from FTP server. Try manually.","ERROR");
  
                                }else
                                    $this->logBlock(" Error moving ".$this->filename." to local folder. Still in tmp folder.","ERROR");
                            
                            } 
                            
                            $PHPWorksheet->garbageCollect();
                            unset($PHPWorksheet);
                            
                            $PHPWorkbook->disconnectWorksheets();
                            $PHPWorkbook->garbageCollect();
                            unset($PHPWorkbook);
                           
                            unset($row);
                            gc_collect_cycles();
                            
                        }else
                            $this->logBlock("Error opening ".$this->filename.". Going to next file..","ERROR");
                        
                         //delete from tmp Folder
                        if (!isset($fs))
                            $fs=new sfFilesystem ();
                        $fs->remove($this->localTmpDir.$this->filename);
                        unset($filter);
                    }else
                        $this->logBlock(" Error downloading ".$this->filename.". Going to next file..","ERROR");  
                } 

            unset($this->filename);
            unset($fileDate);
          }
        }       
        if (!$updateFlag)
            $this->logBlock("No Updates available for ".$plant,"INFO");
        else{
            $this->logBlock($plant." updated: ","INFO");
            foreach ($insertCount as $table => $count)
                $this->logBlock("Inserted ".$count." records for type ".$table,"INFO");
        }
        
        $ftp->close();
        unset($fs);
        unset($ftp);
        seth::regTaskEnd($tasklog, TRUE);        
    }
    
    private function tryInsert($PHPWorksheet,$row,$samIOC_Ind,$dev){
      
      $result=false;
      $class=$dev->getVarTableName();   
      $newVar= new $class();
      $newVar->setDev($dev);
      $newVar->setSource($this->filename.":".$PHPWorksheet->getTitle());
      $newVar->setSourceId($row->getRowIndex());

      foreach ($samIOC_Ind as $wemVar=>$ind){
        if ((strcmp($wemVar,"address")!=0) and (strcmp($wemVar,"0")!=0)) {
            $method="set".$wemVar;
            $newVar->$method($PHPWorksheet->getCellByColumnAndRow($ind-1,$row->getRowIndex())->getValue());
        }                                    
      }                                                                                                                                                   
      try{
        $newVar->save();
        $result=true;
        }catch(Doctrine_Exception $e){}

        $newVar->free(true);
        unset($newVar);

        return $result;
    }
}

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
class IOCReadFilter implements PHPExcel_Reader_IReadFilter
{ 
        private $_sheetFilter = array();

        /**  Get the list of rows and columns to read  */ 
        public function AddIOCSheetFilter($startRow, $endRow, $startCol, $endCol, $sheetname, $headerRow=NULL) { 

            $this->_sheetFilter[$sheetname]=array(
                "startRow"=>$startRow,
                "endRow"=>$endRow,
                "startCol"=>$startCol,
                "endCol"=>$endCol
                );

            if (isset($headerRow))
                $this->_sheetFilter[$sheetname]["headerRow"]=$headerRow;
            else
                $this->_sheetFilter[$sheetname]["headerRow"]=-1;
        } 

        //is this Cell To be Read?
        //only the cell in the worksheet specified in the filter will be read
        public function readCell($column, $row, $worksheetName = '') {
            if (key_exists($worksheetName, $this->_sheetFilter))
                if (($row >= $this->_sheetFilter[$worksheetName]["startRow"] && $row <= $this->_sheetFilter[$worksheetName]["endRow"]) or ($row==$this->_sheetFilter[$worksheetName]["headerRow"]) ) { 
                    if (in_array($column,range($this->_sheetFilter[$worksheetName]["startCol"],$this->_sheetFilter[$worksheetName]["endCol"])))  { 
                        return true; 
                    } 
                } 
            return false; 
        } 

        public function __toString(){

            return print_r($this->_sheetFilter, true);
        }
} 

?>

